package deweb.cimaplanning.reports.corn_position;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import javax.servlet.http.HttpServletRequest;

import com.cs.common.pool.DBDAO;

import deweb.cimaplanning.misc;

public class Report {
	protected int versionId;
	protected Vector periods;
	protected Vector transports;
	protected Vector futures;
	
	protected boolean successful;
	protected String errorMsg;
	
	protected HttpServletRequest request;

	public Report(int versionId, HttpServletRequest request) {
		this.versionId	= versionId;
		this.request	= request;
		
		periods 	= new Vector(24, 12);
		transports	= new Vector(5, 1);
		futures		= new Vector(15, 1);
		
		successful = true;
	}
	
	/*
	 * carga de datos para reporte
	 */
	public void execute(){
		DBDAO dbConn			= null;
		Connection conn 		= null;
		Statement stmt			= null;
		PreparedStatement pstmt	= null;
		ResultSet rs			= null;
		String sql				= null;
		
		try {
		    dbConn	= new DBDAO();
		    conn 	= dbConn.getConnection(misc.DS_NAME);
		    stmt 	= conn.createStatement();
			
			sql = "";
			System.out.println("sql: [" + sql + "]");
			
			//meses que comprende la version			
			sql  = "SELECT mpv.month_period_version_id, mpv.month_period_id,  ";
			sql += "		mp.short_name, mp.month, mp.year ";
			sql += "	FROM months_periods_versions mpv, months_periods mp ";
			sql += "	WHERE mpv.month_period_id = mp.month_period_id ";
			sql += "		AND mpv.version_id = " + versionId + " ";
			
			System.out.println("sql: [" + sql + "]");
			rs = stmt.executeQuery(sql);
			
			MonthlyPeriod mp;
			
			while (rs.next()){
				mp = new MonthlyPeriod();
				
				mp.setMonthPeriodVersionId(rs.getInt("month_period_version_id"));
				mp.setMonthPeriodId(rs.getInt("month_period_id"));
				mp.setShortName(rs.getString("short_name"));
				mp.setMonth(rs.getInt("month"));
				mp.setYear(rs.getInt("year"));
				
				periods.addElement(mp);
			}
			
			periods.trimToSize();
			
			//obtener los transportes de la version
			sql  = "SELECT t.transport_id, t.short_name, t.priority,  ";
			sql += "		SUM(CASE ct.corn_origin WHEN 'I' THEN 1 ELSE 0 END) imported,  ";
			sql += "		SUM(CASE ct.corn_origin WHEN 'N' THEN 1 ELSE 0 END) local ";
			sql += "	FROM vw_v_purchases_orders_versions pov, transports t,  ";
			sql += "		corns_types ct ";
			sql += "	WHERE pov.transport_id = t.transport_id ";
			sql += "		AND pov.corn_type_id = ct.corn_type_id ";
			sql += "		AND pov.version_id = " + versionId + " ";
			sql += "	GROUP BY t.transport_id, t.short_name, t.priority ";
			sql += "	ORDER BY t.priority DESC, t.short_name ";
			
			System.out.println("sql: [" + sql + "]");
			rs = stmt.executeQuery(sql);
			
			Transports transport;
			
			while (rs.next()){
				transport = new Transports();
				
				transport.setTransportId(rs.getInt("transport_id"));
				transport.setShortName(rs.getString("short_name"));
				transport.setPriority(rs.getInt("priority"));
				transport.setImported(rs.getInt("imported") > 0);
				transport.setLocal(rs.getInt("local") > 0);
				
				transports.addElement(transport);
			}
			
			transports.trimToSize();
			
			//obtener las po segun los transportes de la version
			sql  = "SELECT pov.version_id, pov.purchase_order_version_id, pv.month_period_version_id,  ";
			sql += "		pov.transport_id, pov.corn_type_id, pov.code, pov.code_number,  ";
			sql += "		pov.capacity,  ";
			sql += "		CASE WHEN po.bushel_price IS NOT NULL  ";
			sql += "			THEN po.bushel_price ";
			sql += "			ELSE bv.amount END bushel_price,  ";
			sql += "		ct.corn_origin,  ";
			sql += "		cpof.amount future_price_ton,  ";
			sql += "		cpop.amount future_loss_price_ton,  ";
			sql += "		dbo.fn_unit_price_convertion('TON', 'BUS', cpof.amount) future_price_bus,  ";
			sql += "		dbo.fn_unit_price_convertion('TON', 'BUS', cpop.amount) future_loss_price_bus,  ";
			sql += "		ISNULL(pof.quantity, 0) futures_quantity,  ";
			sql += "		ISNULL(pof.wa_price, 0) futures_wa,  ";
			sql += "		ISNULL(pos.quantity, 0) spots_quantity,  ";
			sql += "		ISNULL(pos.wa_price, 0) spots_wa ";
			sql += "	FROM vw_v_purchases_orders_versions pov ";
			sql += "		INNER JOIN vw_periods_versions pv ";
			sql += "			ON pov.biweek_period_version_id = pv.biweek_period_version_id ";
			sql += "		INNER JOIN bushels_versions bv ";
			sql += "			ON pv.month_period_version_id = bv.month_period_version_id ";
			sql += "		INNER JOIN corns_types ct ";
			sql += "			ON pov.corn_type_id = ct.corn_type_id ";
			sql += "		LEFT OUTER JOIN purchases_orders po ";
			sql += "			ON pov.purchase_order_id = po.purchase_order_id ";
			sql += "		LEFT OUTER JOIN costs_po cpof ";
			sql += "			ON pov.purchase_order_version_id = cpof.purchase_order_version_id ";
			sql += "			AND cpof.cost_element_id = (SELECT ce.cost_element_id  ";
			sql += "											FROM costs_elements ce ";
			sql += "											WHERE cpof.cost_element_id = ce.cost_element_id ";
			sql += "												AND ce.short_name = 'FUT') ";
			sql += "		LEFT OUTER JOIN costs_po cpop ";
			sql += "			ON pov.purchase_order_version_id = cpop.purchase_order_version_id ";
			sql += "			AND cpop.cost_element_id = (SELECT ce.cost_element_id  ";
			sql += "											FROM costs_elements ce ";
			sql += "											WHERE cpop.cost_element_id = ce.cost_element_id ";
			sql += "												AND ce.short_name = 'FUT_P') ";
			sql += "		LEFT OUTER JOIN vw_purchases_orders_wa_future pof ";
			sql += "			ON pov.purchase_order_id = pof.purchase_order_id ";
			sql += "		LEFT OUTER JOIN vw_purchases_orders_wa_spot pos ";
			sql += "			ON pov.purchase_order_id = pos.purchase_order_id ";
			sql += "	WHERE pov.version_id = " + versionId + " ";
			sql += "		AND pov.transport_id = ? ";
			sql += "	ORDER BY pov.version_id, pv.month_period_version_id, pov.purchase_order_version_id ";

			System.out.println("sql: [" + sql + "]");
			
			pstmt = conn.prepareStatement(sql);
			
			PurchaseOrder po 	= null;
			Vector poTemp		= null;
			
			for (int i = 0; i < transports.size(); i++){
				transport = (Transports)transports.elementAt(i);
				pstmt.setInt(1, transport.getTransportId());
				
				poTemp = new Vector(10, 5);
				
				System.out.println(" valor [" + transport.getTransportId() + "] sql: [" + sql + "]");
				
				rs = pstmt.executeQuery();
				
				while (rs.next()){
					po = new PurchaseOrder();
					
					
					po.setPurchaseOrderVersionId(rs.getInt("purchase_order_version_id"));
					po.setMonthPeriodVersionId(rs.getInt("month_period_version_id"));
					po.setTransportId(rs.getInt("transport_id"));
					po.setCornTypeId(rs.getInt("corn_type_id"));
					po.setCode(rs.getString("code"));
					po.setCodeNumber(rs.getInt("code_number"));
					po.setCapacity(rs.getFloat("capacity"));
					po.setBushelPrice(rs.getDouble("bushel_price"));
					po.setCornOrigin(rs.getString("corn_origin"));
					po.setFuturesPrice(rs.getDouble("futures_wa"));
					po.setFuturesQuantity(rs.getInt("futures_quantity"));
					po.setSpotPrice(rs.getDouble("spots_wa"));
					po.setSpotQuantity(rs.getInt("spots_quantity"));
					
					//transport.getPo().addElement(po);
					poTemp.addElement(po);
				}
				
				poTemp.trimToSize();
				transport.setPo(poTemp);
				
				transports.insertElementAt(transport, i);
				transports.removeElementAt(i + 1);
			}
			
			transports.trimToSize();
			
			//obtener el listado de los futuros existentes al momento (fuera de version)
			//pero como minimo que tengan fecha de compra el periodo inicial de la version
			sql  = "SELECT cf.account_future_period_id, fp.short_name, fp.year,  ";
			sql += "		SUM(cf.quantity) quantity,  ";
			sql += "		(SUM(cf.quantity * cf.unit_price) / SUM(cf.quantity)) price ";
			sql += "	FROM corn_futures cf, futures_periods fp ";
			sql += "	WHERE cf.account_future_period_id = fp.future_period_id ";
			sql += "		AND cf.account_future_period_id >= (SELECT MIN(pv.future_period_id) ";
			sql += "												FROM vw_periods_versions pv ";
			sql += "												WHERE pv.version_id = " + versionId + ") ";
			sql += "	GROUP BY cf.account_future_period_id, fp.short_name, fp.year ";
			sql += "	ORDER BY cf.account_future_period_id ";
			
			System.out.println("sql: [" + sql + "]");
			rs = stmt.executeQuery(sql);
			
			Futures f;
			
			while (rs.next()){
				f = new Futures();
				
				f.setAccountFuturePeriodId(rs.getInt("account_future_period_id"));
				f.setShortName(rs.getString("short_name"));
				f.setYear(rs.getInt("year"));
				f.setQuantity(rs.getInt("quantity"));
				f.setPrice(rs.getDouble("price"));
				
				futures.addElement(f);
			}
			
			futures.trimToSize();
			
			
		} catch (Exception e){
			successful	= false;
			errorMsg	= e.getMessage();
		} finally {
		    try { conn.close(); } catch (Exception e) {}
		    try { stmt.close(); } catch (Exception e) {}
		    try { pstmt.close();} catch (Exception e) {}
		    try { rs.close();   } catch (Exception e) {}
		}
	}

	/**
	 * @return the successful
	 */
	public boolean isSuccessful() {
		return successful;
	}

	/**
	 * @return the errorMsg
	 */
	public String getErrorMsg() {
		return errorMsg;
	}

	/**
	 * @return the versionId
	 */
	public int getVersionId() {
		return versionId;
	}

	/**
	 * @param versionId the versionId to set
	 */
	public void setVersionId(int versionId) {
		this.versionId = versionId;
	}

	/**
	 * @return the periods
	 */
	public Vector getPeriods() {
		return periods;
	}

	/**
	 * @param periods the periods to set
	 */
	public void setPeriods(Vector periods) {
		this.periods = periods;
	}

	/**
	 * @return the transports
	 */
	public Vector getTransports() {
		return transports;
	}

	/**
	 * @param transports the transports to set
	 */
	public void setTransports(Vector transports) {
		this.transports = transports;
	}

	/**
	 * @return the futures
	 */
	public Vector getFutures() {
		return futures;
	}

	/**
	 * @param futures the futures to set
	 */
	public void setFutures(Vector futures) {
		this.futures = futures;
	}

}
